package mudbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import mudbconnection.DBPhysicalSimpleSummaryService.PhysicalSimpleSummaryMapper;
import muobject.PhysicalSimpleSummary;
import muobject.QuestionnaireSimpleSummary;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBQuestionnaireSimpleSummaryService")
public class DBQuestionnaireSimpleSummaryService implements DBQuestionnaireSimpleSummary {
	
	private JdbcTemplate jdbct;

	@Autowired
	public DBQuestionnaireSimpleSummaryService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public Boolean ExistDataTable(){
		int tableCount = jdbct.queryForInt("Select count(*) from TempQuestionnaireSimpleSummary");
		return tableCount>0 ? true : false;
	}
	
	public List<QuestionnaireSimpleSummary> getAllQuestionnaireSimpleSummary(){
		@SuppressWarnings("unchecked")
		List<QuestionnaireSimpleSummary> questionnaireSimpleSummarys = (List<QuestionnaireSimpleSummary>)jdbct
				.query("Select * from TempQuestionnaireSimpleSummary",
				new QuestionnaireSimpleSummaryMapper());
		return questionnaireSimpleSummarys;
	}
	
	public boolean setQuestionnaireSimpleSummary (QuestionnaireSimpleSummary questionnaireSimpleSummary) {
		try{
			jdbct.update("insert into TempQuestionnaireSimpleSummary(year, grade, sclass, stno, classno, ename," +
					" cname, sex, age, testdate, d1score, d2score, d3score, d4score, d5score, d6score, " +
					" d7score, d8score, d9score,  d10score, d11score, d12score, d13score, d14score, d15score, " +
					" dtotalscore, f1score, f2score, f3score, f4score, f5score, f6score, f7score, f8score, " +
					" f9score, f10score, ftotalscore) " +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", 
				     new Object[] { questionnaireSimpleSummary.getYear(), questionnaireSimpleSummary.getGrade(),
					questionnaireSimpleSummary.getSclass(), questionnaireSimpleSummary.getStno(), questionnaireSimpleSummary.getClassno(),
					questionnaireSimpleSummary.getEname(), questionnaireSimpleSummary.getCname(), questionnaireSimpleSummary.getSex(),
					questionnaireSimpleSummary.getAge(), questionnaireSimpleSummary.getTestdate(), questionnaireSimpleSummary.getD1score(),
					questionnaireSimpleSummary.getD2score(), questionnaireSimpleSummary.getD3score(), questionnaireSimpleSummary.getD4score(),
					questionnaireSimpleSummary.getD5score(), questionnaireSimpleSummary.getD6score(), 
					questionnaireSimpleSummary.getD7score(), questionnaireSimpleSummary.getD8score(), questionnaireSimpleSummary.getD9score(),
					questionnaireSimpleSummary.getD10score(),
					questionnaireSimpleSummary.getD11score(), questionnaireSimpleSummary.getD12score(), questionnaireSimpleSummary.getD13score(),
					questionnaireSimpleSummary.getD14score(), questionnaireSimpleSummary.getD15score(), 
					questionnaireSimpleSummary.getDtotalscore(), questionnaireSimpleSummary.getF1score(), questionnaireSimpleSummary.getF2score(),
					questionnaireSimpleSummary.getF3score(), questionnaireSimpleSummary.getF4score(), questionnaireSimpleSummary.getF5score(), 
					questionnaireSimpleSummary.getF6score(), questionnaireSimpleSummary.getF7score(), questionnaireSimpleSummary.getF8score(),
					questionnaireSimpleSummary.getF9score(), questionnaireSimpleSummary.getF10score(), questionnaireSimpleSummary.getFtotalscore()});
			//jdbct.getDataSource().getConnection().commit();
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean deleteAllQuestionnaireSimpleSummary() {
		try{
			jdbct.update("delete from TempQuestionnaireSimpleSummary");
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public double getQuestionnaireSexScoreCount(String sex, String item, int score){
		int count;
		try{
			if(score == 0){
				count = jdbct.queryForInt("select count(*) from TempQuestionnaireSimpleSummary where " +
						" Sex like ? and " + item + " <> 0 " , new Object[]{ sex });
			}else{
				count = jdbct.queryForInt("select count(*) from TempQuestionnaireSimpleSummary where Sex like ? " +
					"and " + item + " = ? ", new Object[]{ sex, score});
			}
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	public double getQuestionnaireSexScoreAvg(String sex, String item){
		double count;
		try{
			count = (Double) jdbct.queryForObject("select avg(" + item + ") from TempQuestionnaireSimpleSummary where " +
					" Sex like ? and " + item + " <> 0", new Object[]{ sex }, Double.class);
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	class QuestionnaireSimpleSummaryMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			QuestionnaireSimpleSummary questionnaireSimpleSummary = null;
			try{
				questionnaireSimpleSummary = new QuestionnaireSimpleSummary(rs.getString("year"), rs.getString("grade"), rs.getString("sclass")
						, rs.getString("stno"), rs.getInt("classno"), rs.getString("ename"), rs.getString("cname")
						, rs.getString("sex"), rs.getInt("age"), rs.getString("testdate"), rs.getInt("d1score")
						, rs.getInt("d2score"), rs.getInt("d3score"), rs.getInt("d4score"),rs.getInt("d5score")
						, rs.getInt("d6score"), rs.getInt("d7score"), rs.getInt("d8score"), rs.getInt("d9score")
						, rs.getInt("d10score"), rs.getInt("d11score"), rs.getInt("d12score"),rs.getInt("d13score")
						, rs.getInt("d14score"), rs.getInt("d15score"), rs.getDouble("dtotalscore")
						, rs.getInt("f1score"), rs.getInt("f2score"), rs.getInt("f3score"), rs.getInt("f4score")
						, rs.getInt("f5score"), rs.getInt("f6score"), rs.getInt("f7score"), rs.getInt("f8score")
						, rs.getInt("f9score"), rs.getInt("f10score"), rs.getDouble("ftotalscore"));
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return questionnaireSimpleSummary;
		}
	}
}
